package com.skywolf.chem.common.excel;


import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Array;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class ExcelWriter {

    private static final int MAX_SHEET_ROWNUM = 1000000;//100万

    private static final Logger logger = LoggerFactory.getLogger(ExcelWriter.class);

    /**
     * 导出多个sheet工具类
     * @param defines
     * @param exportObject
     * @param excelType
     * @param outputStream
     * @throws IOException
     */
    public static void writeMulti(Map<String,Collection<CellDefine>> defines , Map<String,Object> exportObject, ExcelTable.ExcelType excelType, OutputStream outputStream) throws IOException {
        writeToMultiBook(defines, exportObject, excelType).write(outputStream);
    }
    public static Workbook writeToMultiBook(Map<String,Collection<CellDefine>> definesTag, Map<String,Object> exportData, ExcelTable.ExcelType excelType) {
        Workbook workbook;
        if (ExcelTable.ExcelType.XLS.equals(excelType)) {
            workbook = new HSSFWorkbook();
        } else {
            workbook = new XSSFWorkbook() ;
        }
        CreationHelper factory = workbook.getCreationHelper();
        int tag = 0;
        for (Map.Entry<String,Object> entry : exportData.entrySet()) {
            Sheet sheet = workbook.createSheet();
            workbook.setSheetName(tag,  entry.getKey());
            Object exportObject = entry.getValue();
            sheet.setDefaultColumnWidth(14);
            Collection<CellDefine> defines = definesTag.get(entry.getKey());
            if (defines == null) {
                defines = getDefaultDefines(exportObject);
            }
            if( defines == null || defines.isEmpty()){
                return workbook;
            }
            createHeaderRow(defines, sheet, createHeaderStyle(workbook),factory);
            createDataRows(defines, sheet, exportObject, createDataStyle(workbook),factory,true,workbook);
//            System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
            tag++;
        }
        return workbook;
    }

    public static void write(Collection<CellDefine> defines, Object exportObject, ExcelTable.ExcelType excelType, OutputStream outputStream) throws IOException {
        writeToBook(defines, exportObject, excelType).write(outputStream);
    }

    public static Workbook writeToBook(Collection<CellDefine> defines, Object exportObject, ExcelTable.ExcelType excelType) {
        Workbook workbook;
        if (ExcelTable.ExcelType.XLS.equals(excelType)) {
            workbook = new HSSFWorkbook();
        } else {
            workbook = new XSSFWorkbook() ;
        }
        CreationHelper factory = workbook.getCreationHelper();
        Sheet sheet = workbook.createSheet();
        sheet.setDefaultColumnWidth(14);

        if (defines == null) {
            defines = getDefaultDefines(exportObject);
        }
        if( defines == null || defines.isEmpty()){
            return workbook;
        }
        createHeaderRow(defines, sheet, createHeaderStyle(workbook),factory);
        createDataRows(defines, sheet, exportObject, createDataStyle(workbook),factory,false,workbook);
        return workbook;
    }
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("微软雅黑");
        font.setBold(true);

        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }
    private static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setFontName("微软雅黑");
        style.setFont(font);
        return style;
    }
    private static Collection<CellDefine> getDefaultDefines(Object exportObject) {
        if (exportObject == null) {
            return null;
        }

        if (exportObject instanceof Map) {
            Set<?> keys = ((Map<?, ?>) exportObject).keySet();
            List<CellDefine> defines = new ArrayList<>(keys.size());
            for (Object obj : keys) {
                defines.add(new CellDefine(obj.toString(),obj.toString()));
            }
            return defines;
        }

        if (exportObject instanceof Collection) {
            Collection<?> objects = (Collection<?>) exportObject;
            if (objects.isEmpty()) {
                return null;
            }
            Object obj = objects.iterator().next();
            return getDefaultDefines(obj);
        }
        Class<?> clazz = exportObject.getClass();

        if (clazz.isEnum()) {
            return null;
        }

        if (clazz.isArray()) {
            int len = Array.getLength(exportObject);
            if (len == 0) {
                return null;
            }
            return getDefaultDefines(Array.get(exportObject, 0));
        }

        List<Method> getters = getMethodsStartWith(exportObject, "get");
        List<CellDefine> defines = new ArrayList<>(getters.size());
        for (Method method : getters) {
            String cellName = lowerFistChar(method.getName().substring(3));
            defines.add(new CellDefine(cellName,cellName));
        }

        return defines;
    }

    private static void createHeaderRow(Collection<CellDefine> defines, Sheet sheet, CellStyle cellStyle, CreationHelper factory) {
        ClientAnchor anchor = factory.createClientAnchor();
        Row headerRow = sheet.createRow(0);
        int count = 0;
        for (CellDefine define : defines) {
            Cell cell = headerRow.createCell(count);

            if(!StringUtils.isEmpty(define.getFormat())){
                //指定format
                DataFormat format = sheet.getWorkbook().createDataFormat();

                CellStyle columnStyle=sheet.getWorkbook().createCellStyle();
                columnStyle.cloneStyleFrom(createDataStyle(sheet.getWorkbook()));
                columnStyle.setDataFormat(format.getFormat(define.getFormat()));
                sheet.setColumnWidth(count, 4000);//这行加上了 就么有隐藏了
                sheet.setDefaultColumnStyle(count,columnStyle); //指定整列format

            }
            if(define.getSelect()!=null){
                setValidation(sheet,define.getSelect(),1,1001,count,count);//默认前1000行有下拉列表
            }

            cell.setCellStyle(cellStyle);
            String cellValue=define.getCellDescr();
            if(StringUtils.isEmpty(cellValue)){
                cellValue=define.getCellName();
            }
            cell.setCellValue(cellValue);
            if(StringUtils.isNotBlank(define.getComment())){
                Drawing p=sheet.createDrawingPatriarch();
                Comment comment = p.createCellComment(anchor);
                RichTextString str0 = factory.createRichTextString(define.getComment());
                comment.setString(str0);
                cell.setCellComment(comment);
            }
            count++;
        }
    }


    private static void createDataRows(Collection<CellDefine> defines, Sheet sheet, Object exportObject, CellStyle cellStyle, CreationHelper factory, boolean isformat, Workbook workbook) {
        if (exportObject == null) {
            return;
        }

        if (exportObject instanceof Collection) {
            Collection<?> collection = (Collection<?>) exportObject;
            int i = 0;
            for (Object object : collection) {
                if (null != object) {
                    if (sheet.getLastRowNum() > MAX_SHEET_ROWNUM) {
                        sheet = sheet.getWorkbook().createSheet();
                        createHeaderRow(defines,sheet,cellStyle,factory);
                    }
                    try {
                        createDataRow(defines, sheet, object, cellStyle, isformat, workbook);
                    } catch (Exception e) {
                        i++;
                        logger.error("write excel error! data: {}, error:{}", object + "", e.getMessage());
                    }
                } else {
                    i++;
                }
            }
            if (i != 0) {
                logger.info("写数据时， 丢弃异常数据：{} 条", i);
            }
            return;
        }

        Class<?> clazz = exportObject.getClass();
        if (clazz.isArray()) {
            for (int i = 0, len = Array.getLength(exportObject); i < len; i++) {
                if (sheet.getLastRowNum() > MAX_SHEET_ROWNUM) {
                    sheet = sheet.getWorkbook().createSheet();
                    createHeaderRow(defines,sheet,cellStyle,factory);
                }
                createDataRow(defines, sheet, Array.get(exportObject, i), cellStyle,isformat,workbook);
            }
            return;
        }
        if (sheet.getLastRowNum() > MAX_SHEET_ROWNUM) {
            sheet = sheet.getWorkbook().createSheet();
            createHeaderRow(defines,sheet,cellStyle,factory);
        }
        createDataRow(defines, sheet, exportObject, cellStyle,isformat,workbook);

    }

    /**
     * 设置 excel的校验
     * @param sheet
     * @param textlist
     * @param firstRow
     * @param endRow
     * @param firstCol
     * @param endCol
     */
    private static void setValidation(Sheet sheet,
                                      String[] textlist, int firstRow, int endRow, int firstCol,
                                      int endCol) {

        CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
        DataValidationHelper helper;

        if(sheet instanceof HSSFSheet) {
            helper = new HSSFDataValidationHelper((HSSFSheet) sheet);
        }
        else {
            helper = new XSSFDataValidationHelper((XSSFSheet)sheet);
        }
        DataValidationConstraint constraint= helper.createExplicitListConstraint(textlist);
        DataValidation data_validation_list  = helper.createValidation(constraint,regions);

        sheet.addValidationData(data_validation_list);
    }
    private static List<Method> getMethodsStartWith(Object object, String startWith) {
        if (object == null) {
            return null;
        }
        Method[] methods = object.getClass().getMethods();
        List<Method> methodList = new ArrayList<Method>();
        for (Method method : methods) {
            if (method.getName().startsWith(startWith) && method.getName().length() > 3 && !method.getName().equals("getClass")) {
                methodList.add(method);
            }
        }
        return methodList;
    }
    private static void createDataRow(Collection<CellDefine> defines, Sheet sheet, Object exportObject, CellStyle cellStyle, boolean isFormat, Workbook workbook) {
        Boolean oldIsFormat = isFormat;
        Row row = sheet.createRow(sheet.getLastRowNum() + 1);
        if (exportObject instanceof Map) {
            Map<?, ?> map = (Map<?, ?>) exportObject;
            for (CellDefine key : defines) {
                if(key.getCellName().equalsIgnoreCase("uid")){
                    isFormat = false;
                }else{
                    isFormat = oldIsFormat;
                }
                createDataCell(row, map.get(key.getCellName()), cellStyle,isFormat,workbook);
            }
            return;
        }

        if (exportObject instanceof Collection) {
            Collection<?> collection = (Collection<?>) exportObject;
            for (Object object : collection) {
                createDataCell(row, object, cellStyle,isFormat,workbook);
            }
            return;
        }

        Class<?> clazz = exportObject.getClass();
        if (clazz.isArray()) {
            for (int i = 0, len = Array.getLength(exportObject); i < len; i++) {
                createDataCell(row, Array.get(exportObject, i), cellStyle,isFormat,workbook);
            }
            return;
        }

        for (CellDefine key : defines) {
            if(key.getCellName().equalsIgnoreCase("uid")){
                isFormat = false;
            }else{
                isFormat = oldIsFormat;
            }
            createDataCell(row, getFieldValue(key.getCellName(), exportObject), cellStyle,isFormat,workbook);
        }
    }
    private static Cell createDataCell(Row row, Object value, CellStyle cellStyle, boolean isformat, Workbook workbook) {
        int cellIndex = row.getLastCellNum() == -1 ? 0 : row.getLastCellNum();
        Cell cell = row.createCell(cellIndex);
        String v = formatToString(value);
        if(isNumeric(v) && isformat){
            cellStyle = createDataStyle(workbook);
            if(v.indexOf(".") > 0) {
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
            }else{
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
            }
            cell.setCellStyle(cellStyle);
            v = v.replaceAll(",","");
            cell.setCellValue(Double.parseDouble(v));
        }else{
            cell.setCellStyle(cellStyle);
            cell.setCellValue(v);
        }
        return cell;
    }

    private static Object getFieldValue(String filedName, Object exportObject) {
        try {
            Class<?> clazz = exportObject.getClass();
            Method getMethod = clazz.getMethod("get" + upperFistChar(filedName));
            return getMethod.invoke(exportObject);
        } catch (Exception e) {
            return null;
        }
    }

    private static String formatToString(Object object) {
        if (object == null) {
            return "";
        } else if (object instanceof Date) {
            return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) object);
        }else {
            return object.toString();
        }
    }

    /**
     * 判断是否数字-整数，小数
     * @param str
     * @return
     */
    public static boolean isNumeric(String str){
        Pattern pattern = Pattern.compile("[0-9]+(.[0-9]+)?");
        Matcher isNum = pattern.matcher(str);
        if( !isNum.matches() ){
            return false;
        }
        return true;
    }

    private static String upperFistChar(String str) {
        return String.valueOf(str.charAt(0)).toUpperCase() + str.substring(1);
    }
    private static String lowerFistChar(String str) {
        return String.valueOf(str.charAt(0)).toLowerCase() + str.substring(1);
    }
}
